package br.com.massai.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.ejb.EJB;
import javax.ejb.Stateless;
import javax.persistence.NoResultException;
import javax.persistence.Query;

import br.com.massai.model.dao.jdbc.ConnectionDB;
import br.com.massai.model.dao.jdbc.ConnectionPool;
import br.com.massai.model.entity.Collaborator;
import br.com.massai.model.entity.Department;
import br.com.massai.model.entity.ProductionGroup;
import br.com.massai.model.exceptions.ErrorCore;
import br.com.massai.model.exceptions.SystemException;
import br.com.massai.view.enums.Role;
import br.com.massai.view.enums.Status;

@Stateless
public class DepartmentDAO extends GenericDAO<Department> implements DepartmentRepository {

	private static final long serialVersionUID = -3461555747128598327L;

	@EJB private ConnectionPool connectionPool;

	public DepartmentDAO() {
		super(Department.class);
	}

	@Override
	@SuppressWarnings({ "unchecked" })
	public List<Department> getBusyDepartmentsByProductionGroup() throws SystemException {
		try{
			String queryStr = "SELECT d FROM Department d WHERE d.productionGroup <> null AND d.deleted = false";
			Query query = super.em.createQuery(queryStr);

			return query.getResultList();

		} catch (SystemException e) {
			throw new SystemException(ErrorCore.ERROR_GET_RECORD, e);
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Department> getDepartmentsByProductionGroup(ProductionGroup productionGroup) throws SystemException {
		try {
			return em.createQuery("SELECT d FROM Department d WHERE d.productionGroup =:productionGroup AND d.deleted = false").setParameter("productionGroup", productionGroup).getResultList();

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Department> getActiveDepartments() throws SystemException {
		try {
			return em.createQuery("SELECT d FROM Department d WHERE d.status =:status AND d.deleted = false").setParameter("status", Status.A).getResultList();

		} catch (SystemException e) {
			throw new SystemException(ErrorCore.ERROR_UNEXPECTED, e);
		}
	}
	
	@Override
	public String getDepartmentNameAlternative(String empCode, String departmentCode) throws SystemException {
		try {
			return ((Department) em.createQuery("SELECT d FROM Department d WHERE d.codeDepartment =:codeDepartment AND d.codeEmp =:codeEmp AND d.status =:status AND d.deleted = false").setParameter("status", Status.A).getSingleResult()).getAlternativeName();

		} catch (NoResultException e) {
			return null;

		} catch (SystemException e) {
			throw new SystemException(ErrorCore.ERROR_UNEXPECTED, e);
		}
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Department> getDepartmentByUser(Collaborator collaborator) throws SystemException {
		try {
			if(collaborator.getRole().equals(Role.A))
				return em.createQuery("SELECT d FROM Department d WHERE d.status =:status AND d.deleted = false ORDER BY d.alternativeName")
						.setParameter("status", Status.A)
						.getResultList();
			else
				return em.createQuery("SELECT d FROM Department d JOIN d.collaborators users WHERE d.status =:status AND d.deleted = false AND :user IN (users) ORDER BY d.alternativeName")
						.setParameter("status", Status.A)
						.setParameter("user", collaborator).getResultList();

		} catch (SystemException e) {
			throw new SystemException(ErrorCore.ERROR_LOADING_DEPARTMENTS);
		}
	}

	@Override
	public Department getDepartmentFORTES(String codeEmp, String codeDepartment) throws SystemException, SQLException {
		Department department = new Department();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;

		try {
			String query = "SELECT DISTINCT emp_codigo, codigo, nome FROM lot WHERE emp_codigo = ? AND codigo = ?";

			conn = connectionPool.getConnection(ConnectionDB.FORTES);
			pstm = conn.prepareStatement(query);
			pstm.setString(1, codeEmp);
			pstm.setString(2, codeDepartment);
			rs = pstm.executeQuery();

			while(rs.next()){
				department.setCodeEmp(rs.getString("emp_codigo"));
				department.setCodeDepartment(rs.getString("codigo"));
				department.setNameDepartment(rs.getString("nome"));
			}

			return department;

		} catch (SQLException e) {
			throw new SystemException(ErrorCore.ERROR_DATABASE_CONNECTION);

		} catch (Throwable e) {
			throw new SystemException(ErrorCore.ERROR_UNEXPECTED);

		} finally {
			if(conn != null) conn.close();
			if(pstm != null) pstm.close();
			if(rs != null) rs.close();
		}
	}
}
